using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

namespace BuyRequest
{

public class BuyRequestLibs
{
    private string conectionString  = "";
    private SqlCommand command      = null;
    private SqlConnection conection = null;
    private SqlDataAdapter adapter  = null;

    public BuyRequestLibs(string conString)
	{
        conectionString = conString;
	}

    public DataTable GetCountry()
    {
        DataTable dt = null;
        try
        {
            dt = new DataTable();
            string sql = "select DISTINCT * from tm_country";

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
        return dt;
    }

    public DataTable GetProductType()
    {
        DataTable dt = null;
        try
        {
            dt = new DataTable();
            string sql = "select DISTINCT * from tm_productType";

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
        return dt;
    }

    public DataTable GetRevenueUnit()
    {
        DataTable dt = null;
        try
        {
            dt = new DataTable();
            string sql = "select DISTINCT * from tm_currencyUnit ";

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
        return dt;
    }

    public DataTable GetCurrency()
    {
        DataTable dt = null;
        try
        {
            dt = new DataTable();
            string sql = "select DISTINCT * from tm_currency ";

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
        return dt;
    }

    public DataTable GetBuyRequestStatus()
    {
        DataTable dt = null;
        try
        {
            dt = new DataTable();
            string sql = "select DISTINCT * from tm_buyStatus ";

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
        return dt;
    }

    public DataTable GetGradeLevel()
    {
        DataTable dt = null;
        try
        {
            dt = new DataTable();
            string sql = "select DISTINCT * from tm_gradeLevel ";

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
        return dt;
    }

    public string getUserAccByUserId(int userId)
    {
        string rs = "";
        try
        {
            DataTable dt = new DataTable();
            string sql = "SELECT account " +
                            " FROM tm_users " +
                            " WHERE userId = " + userId;

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);

            rs = (string)dt.Rows[0]["account"];

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
        return rs;
    }

    public DataTable getRequestInforTextByRequestID(string requestID)
    {
        DataTable dt = null;
        try
        {
            dt = new DataTable();
            string sql = "select  * from tm_productInfo_text where productId = " + requestID;

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
        
        return dt;
    }

    public void DeleteRequestInforText(string productInforId)
    {
        try
        {
            string sql = "Delete  tm_productInfo_text where id = " + productInforId;

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            int i = command.ExecuteNonQuery();

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
     }

    public void InsertRequestInforText(string productId, string inforCode, string inforText)
    {
        try
        {
            string sql = "INSERT INTO tm_productInfo_text(productId,infoCode,infoText) " +
                        " VALUES (" + productId + "," + inforCode + ",'" + inforText + "')";

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            int i = command.ExecuteNonQuery();

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
    }

    public DataTable getProductInforNumericByProductID(string productID)
    {
        DataTable dt = null;
        try
        {
            dt = new DataTable();
            string sql = "select  * from tm_productInfo_numeric where productId = " + productID;

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }

        return dt;
    }

    public void DeleteProductInforNumeric(string productInforId)
    {
        try
        {
            string sql = "Delete  tm_productInfo_numeric where id = " + productInforId;

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            int i = command.ExecuteNonQuery();

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
    }

    public void InsertProductInforNumeric(string productId, string inforCode, string inforNumeric)
    {
        try
        {
            string sql = "INSERT INTO tm_productInfo_numeric(productId,infoCode,infoValue) " +
                        " VALUES (" + productId + "," + inforCode + "," + inforNumeric + ")";

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            int i = command.ExecuteNonQuery();

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
    }

    public void DeleteCategoryByRequestId(string requestId)
    {
        string sql = "DELETE tm_buy_request_syscodes WHERE requestId =" + requestId;
        Common.sysLibs.ExecuteSQLCmd(sql, conectionString);
    }

    public void InsertCategory(string requestId, string sysCodeId)
    {
        string sql = "INSERT INTO tm_buy_request_syscodes(requestId,syscodeId) " +
                    " VALUES (" + requestId + "," + sysCodeId + ")";
        Common.sysLibs.ExecuteSQLCmd(sql, conectionString);
    }

    public DataTable GetCategory(string requestId)
    {
        DataTable dt = null;
        try
        {
            dt = new DataTable();
            string sql = "SELECT tm_productCat.code,tm_buy_request_syscodes.syscodeId " +
                            " ,tm_productCat.description1 " +
                            " ,tm_buy_request_syscodes.Id " +
                        " FROM tm_buy_request_syscodes RIGHT OUTER JOIN " +
                        " tm_productCat ON tm_buy_request_syscodes.syscodeId = tm_productCat.syscodeId " +
                        " WHERE (tm_buy_request_syscodes.requestId = " + requestId + ")";

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }

        return dt;
    }

    public DataTable getSanPhamTuongTu(string productID)
    {
        DataTable dt = null;
        try
        {
            dt = new DataTable();
            string sql = "SELECT tm_related_product.relatedProdId " +
                            " ,tm_related_product.id " +
                            " ,tm_product.Name1 " +
                            " ,tm_product.productId " +
                        " FROM tm_product INNER JOIN " +
                        " tm_related_product ON tm_product.productId = tm_related_product.relatedProdId " +
                        " WHERE tm_related_product.productId = " + productID;

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }

        return dt;
    }

    public void DeleteSanPhamTuongTu(string Id)
    {
        try
        {
            string sql = "Delete  tm_related_product where id = " + Id;

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            int i = command.ExecuteNonQuery();

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
    }

    public void InsertSanPhamTuongTu(string productId, string productRelatedID)
    {
        try
        {
            string sql = "INSERT INTO tm_related_product(productId,relatedProdId) " +
                        " VALUES (" + productId + "," + productRelatedID + ")";

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            int i = command.ExecuteNonQuery();

            conection.Close();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
    }

    
    /*----------User for Product List and Seacrch Product -------*/
    public DataTable selectRequestWithParameter(string productCode, string Seller,string Status,string productType, string Category,string FromDate, string ToDate)
    {
        DataTable dt = null;
        try
        {
            string sql = " SELECT tm_buy_request.requestId " +
                            " , tm_buy_request.productCode " +            
                            " , tm_buy_request.Name1 " +
                            " , tm_buy_request.Description " +
                            " , tm_buy_request.keywords " +
                            " , tm_buy_request.RegisterDate " +
                            " , tm_buy_request.ExpireDate " +
                            " , tm_buy_request.status " +
                            " , tm_users.account " +
                            " , tm_users.fullName " +
                            " , tm_users.companyName1 " +
                            " , tm_users.userType " +
                            " , tm_productStatus.description1 " +
                        " FROM tm_buy_request INNER JOIN " +
                        " tm_users ON tm_buy_request.RegisterBy = tm_users.userId INNER JOIN " +
                        " tm_productStatus ON tm_buy_request.status = tm_productStatus.statusCode " +
                        " INNER JOIN tm_productType ON tm_buy_request.productType = tm_productType.code ";

            if (Category != "")
            {
                sql += " INNER JOIN (SELECT tm_buy_request_syscodes.requestId " +
                        " ,tm_spccode.description1 " +
                        " FROM tm_buy_request_syscodes RIGHT OUTER JOIN " +
                        " tm_spccode ON tm_buy_request_syscodes.syscodeId = tm_spccode.syscodeId) A ON tm_buy_request.requestId = A.requestId";
            }


            SqlParameter paraProductCode;
            SqlParameter paraSeller;
            SqlParameter paraStatus;
            SqlParameter paraProductType;
            SqlParameter paraCategory;
            SqlParameter paraFromDate;
            SqlParameter paraToDate;

            dt = new DataTable();

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.Connection = conection;

            if ( productCode !="" || Seller != "" || Status !="" || productType !="" || Category !="" || FromDate != "" || ToDate != "")
            {
                sql += " WHERE ";

                if (productCode != "")
                {
                    sql += " tm_buy_request.productCode like N'%'+ @productCode + '%' OR ";

                    paraProductCode         = new SqlParameter();
                    paraProductCode.DbType  = DbType.String;
                    paraProductCode.ParameterName = "@productCode";
                    paraProductCode.Value   = productCode;

                    command.Parameters.Add(paraProductCode);
                }

                if (Seller != "")
                {
                    sql += " tm_users.fullName like N'%'+ @seller+ '%' OR ";

                    paraSeller                  = new SqlParameter();
                    paraSeller.DbType           = DbType.String;
                    paraSeller.ParameterName    = "@seller";
                    paraSeller.Value            = Seller;

                    command.Parameters.Add(paraSeller);
                }

                if (Status != "")
                {
                    sql += " tm_productStatus.description1 like N'%' + @Status + '%' OR ";

                    paraStatus = new SqlParameter();
                    paraStatus.DbType = DbType.String;
                    paraStatus.ParameterName = "@Status";
                    paraStatus.Value = Status;

                    command.Parameters.Add(paraStatus);
                }


                if (productType != "")
                {
                    sql += " tm_productType.description1 like N'%' + @productType + '%' OR ";

                    paraProductType         = new SqlParameter();
                    paraProductType.DbType  = DbType.String;
                    paraProductType.ParameterName = "@productType";
                    paraProductType.Value   = productType;

                    command.Parameters.Add(paraProductType);
                }

                if (Category != "")
                {
                    sql += " A.description1 like N'%' + @Category + '%' OR ";

                    paraCategory                = new SqlParameter();
                    paraCategory.DbType         = DbType.String;
                    paraCategory.ParameterName  = "@Category";
                    paraCategory.Value          = Category;

                    command.Parameters.Add(paraCategory);
                }
                
                
                if (FromDate != "" && ToDate != "")
                {
                    sql += " tm_buy_request.RegisterDate BETWEEN @FromDate AND @ToDate OR ";

                    paraFromDate = new SqlParameter();
                    paraFromDate.DbType = DbType.String;
                    paraFromDate.ParameterName = "@FromDate";
                    paraFromDate.Value = FromDate;

                    paraToDate = new SqlParameter();
                    paraToDate.DbType = DbType.String;
                    paraToDate.ParameterName = "@ToDate";
                    paraToDate.Value = ToDate;

                    command.Parameters.Add(paraFromDate);
                    command.Parameters.Add(paraToDate);
                }

                sql = sql.Substring(0,sql.LastIndexOf("OR"));

                if (Status == "all" || productType == "all")
                {
                    sql = sql.Substring(0, sql.LastIndexOf("WHERE"));
                }

            }

            command.CommandText = sql;


            adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
        return dt;
    }

    public DataTable selectRequestWithSysCodeID(string syscodeId)
    {
        DataTable dt = null;
        try
        {
            string sql = " SELECT tm_buy_request.requestId " +
                            " , tm_buy_request.productCode " +
                            " , tm_buy_request.Name1  " +
                            " , tm_buy_request.Description " +
                            " , tm_buy_request.keywords " +
                            " , tm_buy_request.RegisterDate " +
                            " , tm_buy_request.ExpireDate  " +
                            " , tm_buy_request.status  " +
                            " , tm_users.account  " +
                            " , tm_users.fullName " +
                            " , tm_users.companyName1 " +
                            " , tm_users.userType " +
                            " , tm_productStatus.description1 " +
                        " FROM tm_buy_request INNER JOIN  tm_users ON tm_buy_request.RegisterBy = tm_users.userId " +
                        " INNER JOIN  tm_productStatus ON tm_buy_request.status = tm_productStatus.statusCode  " +
                        " INNER JOIN tm_buy_request_syscodes ON tm_buy_request_syscodes.requestId = tm_buy_request.requestId " +
                        " WHERE tm_buy_request_syscodes.syscodeId = " + syscodeId +
                        " AND  tm_buy_request.status = '" + Common.consts.constStatusENABLE + "'";


            dt = new DataTable();

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.Connection = conection;
            command.CommandText = sql;


            adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
        return dt;
    }

    public void UpdateBuyRequestStatus(string requestId, string status)
    {
        try
        {
            string sql = "UPDATE dbo.tm_buy_request SET [status] = '" + status + "'" +
                         " WHERE [requestId] = " + requestId;

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            int i = command.ExecuteNonQuery();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
    }

    public void inserttm_user_profiles(string UserID,string infoType, string infoId,DateTime onDate )
    {
        try
        {
            string sql = " INSERT INTO tm_user_profiles(userId,infoType,infoId,onDate) " +
                            " VALUES (" + UserID + ",'" + infoType + "'," + infoId + ",'" + onDate.ToString() + "') ";

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.CommandText = sql;
            command.Connection = conection;

            int i = command.ExecuteNonQuery();
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
    }

    public bool isHasInMySpace(string UserID, string infoType, string infoId)
    {
        bool rs = false;
        try
        {
            string sql = " SELECT * " +
                        " FROM tm_user_profiles " +
                        " WHERE tm_user_profiles.userId = " + UserID +
                        " AND  tm_user_profiles.infoType = '" + infoType + "'" +
                        " AND  tm_user_profiles.infoId = " + infoId;


            DataTable dt = new DataTable();

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.Connection = conection;
            command.CommandText = sql;


            adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);

            if (dt.Rows.Count > 0)
                rs = true;
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
        return rs;
    }

    public bool isProductOfUser(string UserID, string productID)
    {
        bool rs = false;
        try
        {
            string sql = " SELECT * " +
                        " FROM tm_buy_request " +
                        " WHERE tm_buy_request.RegisterBy = " + UserID +
                        " AND  tm_buy_request.requestId = " + productID;


            DataTable dt = new DataTable();

            conection = new SqlConnection();
            conection.ConnectionString = conectionString;
            conection.Open();

            command = new SqlCommand();
            command.Connection = conection;
            command.CommandText = sql;


            adapter = new SqlDataAdapter();
            adapter.SelectCommand = command;
            adapter.Fill(dt);

            if (dt.Rows.Count > 0)
                rs = true;
        }
        catch (Exception exc)
        {
            throw exc;
        }
        finally
        {
            if (conection != null)
            {
                conection.Close();
                conection = null;
            }
            command = null;
            adapter = null;
        }
        return rs;
    }
}
}